package com.hellogood.yaohuo.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.shared.entity.Kc;
import com.hellogood.yaohuo.shared.vo.KcVo;

public class KcDAO extends DBHelper {
	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {

		List<Kc> list = new ArrayList<Kc>();
		if (rs != null) {
			while (rs.next()) {
				Kc kc = new Kc();
				kc.setBra(rs.getString("bra") == null ? "" : rs.getString("bra").trim());
				kc.setColor(rs.getString("color") == null ? "" : rs.getString("color").trim());
				kc.setCounts(rs.getInt("Counts"));
				kc.setOrgCode(rs.getString("orgCode") == null ? "" : rs.getString("orgCode").trim());
				kc.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				kc.setRemark(rs.getString("remark") == null ? "" : rs.getString("remark").trim());
				kc.setSize(rs.getString("cSize") == null ? "" : rs.getString("cSize").trim());
				list.add(kc);
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Kc> getKcBy(String orgCode, String pluCode, String bra, String color, String size) {
		String sql = "SELECT * FROM Kc WHERE bra = '" + bra + "' AND color = '" + color + "' AND pluCode = '" + pluCode + "' AND orgCode = '"
				+ orgCode + "' AND cSize = '" + size + "'";
		if (bra == null || bra.equals("") || bra.equals("无")) {
			sql = "SELECT * FROM Kc WHERE color = '" + color + "' AND pluCode = '" + pluCode + "' AND orgCode = '" + orgCode + "' AND cSize = '"
					+ size + "'";
		}
		List<?> list = super.query(sql);
		return (List<Kc>) list;
	}

	@SuppressWarnings("unchecked")
	public List<Kc> getKc(String orgCode, String pluCode, String bra, String color) {
		String sql = "SELECT * FROM Kc WHERE bra = '" + bra + "' AND color = '" + color + "' AND pluCode = '" + pluCode + "' AND orgCode = '"
				+ orgCode + "'";
		if (bra == null || bra.equals("") || bra.equals("无")) {
			sql = "SELECT * FROM Kc WHERE color = '" + color + "' AND pluCode = '" + pluCode + "' AND orgCode = '" + orgCode + "'";
		}
		List<?> list = super.query(sql);
		return (List<Kc>) list;
	}

	public List<KcVo> searchKcVo(String orgCode, String code) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT *,(SELECT depName FROM " + hxDbName + "..dept d WHERE d.depCode = g.depCode) as depName,(SELECT ClsName FROM "
				+ hxDbName + "..GClass c WHERE c.ClsCode = g.ClsCode) as ClsName FROM " + hxDbName + "..Goods g,Kc k WHERE (g.pluCode = '" + code
				+ "' OR g.barCode = '" + code + "' OR g.pluName ='" + code + "') AND g.pluCode = k.pluCode";

		if (code.equals("") || code == null) {
			sql = "SELECT *,(SELECT depName FROM " + hxDbName + "..dept d WHERE d.depCode = g.depCode) as depName,(SELECT ClsName FROM " + hxDbName
					+ "..GClass c WHERE c.ClsCode = g.ClsCode) as ClsName FROM " + hxDbName + "..Goods g,Kc k WHERE g.pluCode = k.pluCode";
		}
//		Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<KcVo> voList = new ArrayList<KcVo>();
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				// "编码", "条码", "部门", "品类", "颜色", "尺寸", "数量", "进价金额", "售价金额"
				KcVo vo = new KcVo();
				vo.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				vo.setDepCode(rs.getString("DepCode") == null ? "" : rs.getString("DepCode").trim());
				vo.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				vo.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				vo.setDepCode(rs.getString("depCode") == null ? "" : rs.getString("depCode").trim());
				vo.setDepName(rs.getString("depName") == null ? "" : rs.getString("depName").trim());
				vo.setClsCode(rs.getString("clsCode") == null ? "" : rs.getString("clsCode").trim());
				vo.setClsName(rs.getString("clsName") == null ? "" : rs.getString("clsName").trim());
				vo.setColor(rs.getString("color") == null ? "" : rs.getString("color").trim());
				vo.setcSize(rs.getString("cSize") == null ? "" : rs.getString("cSize").trim());
				vo.setCount(rs.getString("counts") == null ? "" : rs.getString("counts").trim());
				vo.setjPrice(rs.getString("jPrice") == null ? "" : rs.getString("jPrice").trim());
				vo.setPrice(rs.getString("price") == null ? "" : rs.getString("price").trim());
				voList.add(vo);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
//				ConnectionPool.getInstance().release(conn);
			}
		}
		return voList;
	}

	public int searchKCTotalNumber(String orgCode, String code) {
		int number = 0;
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT COUNT(*) AS number FROM " + hxDbName + "..Goods g,Kc k WHERE (g.pluCode = '" + code + "' OR g.barCode = '" + code
				+ "' OR g.pluName ='" + code + "') AND g.pluCode = k.pluCode";

		if (code.equals("") || code == null) {
			sql = "SELECT COUNT(*) AS number FROM " + hxDbName + "..Goods g,Kc k WHERE g.pluCode = k.pluCode";
		}
//		Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			 conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				number = rs.getInt("number");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
//				ConnectionPool.getInstance().release(conn);
			}
		}

		return number;
	}

	public List<KcVo> searchKcVoByPage(String orgCode, String code, int page, int pageSize) {
		String hxDbName = ConnectionPool.hxDbName;

		String sql = "SELECT IDENTITY(INT,1,1) id,k.*,g.barCode,g.pluname,g.depcode,g.clsCode,g.jprice,g.price,(SELECT depName FROM " + hxDbName
				+ "..dept d WHERE d.depCode = g.depCode) as depName,(SELECT ClsName FROM " + hxDbName
				+ "..GClass c WHERE c.ClsCode = g.ClsCode) as ClsName INTO tempdb..tempyh FROM  " + hxDbName + "..Goods g,Kc k WHERE (g.pluCode = '"
				+ code + "' OR g.barCode = '" + code + "' OR g.pluName ='" + code + "') AND g.pluCode = k.pluCode ";

		if (code.equals("") || code == null) {
			sql = "SELECT SELECT IDENTITY(INT,1,1) id,k.*,g.barCode,g.pluname,g.depcode,g.clsCode,g.jprice,g.price,(SELECT depName FROM " + hxDbName
					+ "..dept d WHERE d.depCode = g.depCode) as depName,(SELECT ClsName FROM " + hxDbName
					+ "..GClass c WHERE c.ClsCode = g.ClsCode) as ClsName INTO tempdb..tempyh FROM " + hxDbName
					+ "..Goods g,Kc k WHERE g.pluCode = k.pluCode";
		}

		int start = page * pageSize;

//		Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<KcVo> voList = new ArrayList<KcVo>();
		try {
			conn = DBConnection.getInstance().getConnection();
			stmt = conn.createStatement();
			stmt.execute(sql);
			sql = "SELECT TOP " + pageSize + " * FROM tempdb..tempyh WHERE id NOT IN (SELECT TOP " + start
					+ " id FROM tempdb..tempyh ORDER BY id) ORDER BY id";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				// "编码", "条码", "部门", "品类", "颜色", "尺寸", "数量", "进价金额", "售价金额"
				KcVo vo = new KcVo();
				vo.setBarCode(rs.getString("barCode") == null ? "" : rs.getString("barCode").trim());
				vo.setDepCode(rs.getString("DepCode") == null ? "" : rs.getString("DepCode").trim());
				vo.setPluCode(rs.getString("pluCode") == null ? "" : rs.getString("pluCode").trim());
				vo.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				vo.setDepCode(rs.getString("depCode") == null ? "" : rs.getString("depCode").trim());
				vo.setDepName(rs.getString("depName") == null ? "" : rs.getString("depName").trim());
				vo.setClsCode(rs.getString("clsCode") == null ? "" : rs.getString("clsCode").trim());
				vo.setClsName(rs.getString("clsName") == null ? "" : rs.getString("clsName").trim());
				vo.setColor(rs.getString("color") == null ? "" : rs.getString("color").trim());
				vo.setcSize(rs.getString("cSize") == null ? "" : rs.getString("cSize").trim());
				vo.setCount(rs.getString("counts") == null ? "" : rs.getString("counts").trim());
				vo.setjPrice(rs.getString("jPrice") == null ? "" : rs.getString("jPrice").trim());
				vo.setPrice(rs.getString("price") == null ? "" : rs.getString("price").trim());
				voList.add(vo);
			}
			sql = "If Exists(SELECT NAME FROM tempdb..sysobjects WHERE NAME='tempyh' AND XType='U') DROP TABLE tempdb..tempyh";
			stmt.execute(sql);
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
//				ConnectionPool.getInstance().release(conn);
			}
		}
		return voList;
	}

	@SuppressWarnings("unchecked")
	public List<Kc> searchKC(String orgCode, String code) {
		String sql = "SELECT * FROM Kc WHERE orgCode = '" + orgCode + "' and pluCode='" + code + "'";
		if (code == null || code.equals("")) {
			sql = "Select * from KC where orgCode = '" + orgCode + "'";
		}
		List<?> list = super.query(sql);
		return (List<Kc>) list;
	}
}
